import { ScriptService } from './ScriptService';
import { ExecutionService } from './ExecutionService';
import { ConnectionService } from './ConnectionService';
import { FolderService } from './FolderService';
import JSZip from 'jszip';
import * as XLSX from 'xlsx';
import { createObjectCsvWriter } from 'csv-writer';
import path from 'path';
import fs from 'fs/promises';
import { logger } from '../utils/logger';

export interface ImportScriptOptions {
  folder_id?: string;
  created_by?: string;
  overwrite_existing?: boolean;
  validate_syntax?: boolean;
}

export interface ExportScriptOptions {
  format: 'zip' | 'json';
  include_versions?: boolean;
  include_metadata?: boolean;
}

export interface ExportResultOptions {
  format: 'csv' | 'excel' | 'json';
  include_metadata?: boolean;
  max_rows?: number;
}

export interface BackupOptions {
  include_scripts?: boolean;
  include_connections?: boolean;
  include_folders?: boolean;
  include_executions?: boolean;
  date_range?: {
    start_date: Date;
    end_date: Date;
  };
}

export interface ImportResult {
  success_count: number;
  failed_count: number;
  imported_items: any[];
  errors: string[];
  warnings: string[];
}

export class ImportExportService {
  private scriptService: ScriptService;
  private executionService: ExecutionService;
  private connectionService: ConnectionService;
  private folderService: FolderService;

  constructor() {
    this.scriptService = new ScriptService();
    this.executionService = new ExecutionService();
    this.connectionService = new ConnectionService();
    this.folderService = new FolderService();
  }

  // ==================== 脚本导入 ====================

  /**
   * 批量导入脚本文件
   */
  async importScripts(
    files: Express.Multer.File[], 
    options: ImportScriptOptions = {}
  ): Promise<ImportResult> {
    const result: ImportResult = {
      success_count: 0,
      failed_count: 0,
      imported_items: [],
      errors: [],
      warnings: []
    };

    logger.info(`开始导入 ${files.length} 个脚本文件`);

    for (const file of files) {
      try {
        await this.importSingleScript(file, options, result);
      } catch (error: any) {
        result.failed_count++;
        result.errors.push(`文件 ${file.originalname}: ${error.message}`);
        logger.error(`导入文件失败: ${file.originalname}`, error);
      }
    }

    logger.info(`脚本导入完成: 成功 ${result.success_count}, 失败 ${result.failed_count}`);
    return result;
  }

  private async importSingleScript(
    file: Express.Multer.File,
    options: ImportScriptOptions,
    result: ImportResult
  ): Promise<void> {
    const content = file.buffer.toString('utf-8');
    const fileName = file.originalname;
    const name = path.parse(fileName).name;

    // 验证文件格式
    if (!this.validateScriptFile(file, content)) {
      throw new Error('不支持的文件格式或内容无效');
    }

    // 语法验证（如果启用）
    if (options.validate_syntax) {
      const syntaxErrors = await this.validateSQLSyntax(content);
      if (syntaxErrors.length > 0) {
        result.warnings.push(`文件 ${fileName} 存在语法警告: ${syntaxErrors.join(', ')}`);
      }
    }

    // 检查名称冲突
    let finalName = name;
    if (!options.overwrite_existing) {
      let counter = 1;
      while (!(await this.scriptService.isNameUniqueInFolder(finalName, options.folder_id))) {
        finalName = `${name}_${counter}`;
        counter++;
      }
    }

    // 创建脚本
    const scriptData = {
      name: finalName,
      content,
      description: `从文件 ${fileName} 导入`,
      tags: ['导入', this.getFileTypeTag(fileName)],
      folder_id: options.folder_id,
      created_by: options.created_by
    };

    const script = await this.scriptService.createScript(scriptData);
    result.success_count++;
    result.imported_items.push({
      type: 'script',
      id: script.id,
      name: script.name,
      original_filename: fileName
    });
  }

  /**
   * 从ZIP文件导入脚本
   */
  async importScriptsFromZip(
    zipBuffer: Buffer,
    options: ImportScriptOptions = {}
  ): Promise<ImportResult> {
    const zip = new JSZip();
    const zipContent = await zip.loadAsync(zipBuffer);

    const files: Express.Multer.File[] = [];
    
    for (const [filename, file] of Object.entries(zipContent.files)) {
      if (!file.dir && this.isSupportedScriptFile(filename)) {
        const content = await file.async('nodebuffer');
        files.push({
          originalname: filename,
          buffer: content,
          mimetype: 'text/plain',
          size: content.length
        } as Express.Multer.File);
      }
    }

    return await this.importScripts(files, options);
  }

  /**
   * 从JSON配置导入
   */
  async importFromBackup(
    backupData: any,
    options: { 
      restore_scripts?: boolean;
      restore_connections?: boolean;
      restore_folders?: boolean;
      created_by?: string;
    } = {}
  ): Promise<ImportResult> {
    const result: ImportResult = {
      success_count: 0,
      failed_count: 0,
      imported_items: [],
      errors: [],
      warnings: []
    };

    try {
      // 验证备份数据格式
      if (!this.validateBackupData(backupData)) {
        throw new Error('备份数据格式无效');
      }

      // 导入文件夹结构
      if (options.restore_folders && backupData.folders) {
        await this.importFolders(backupData.folders, result);
      }

      // 导入连接配置
      if (options.restore_connections && backupData.connections) {
        await this.importConnections(backupData.connections, result);
      }

      // 导入脚本
      if (options.restore_scripts && backupData.scripts) {
        await this.importScriptsFromBackup(backupData.scripts, result, options.created_by);
      }

    } catch (error: any) {
      result.errors.push(`导入备份失败: ${error.message}`);
      logger.error('导入备份失败', error);
    }

    return result;
  }

  // ==================== 脚本导出 ====================

  /**
   * 导出脚本为ZIP文件
   */
  async exportScripts(
    scriptIds: string[],
    options: ExportScriptOptions = { format: 'zip' }
  ): Promise<{ buffer: Buffer; filename: string; contentType: string }> {
    logger.info(`开始导出 ${scriptIds.length} 个脚本`);

    const scripts = [];
    for (const scriptId of scriptIds) {
      const script = await this.scriptService.getScript(scriptId);
      if (script) {
        scripts.push(script);
        
        // 包含版本历史
        if (options.include_versions) {
          const versions = await this.scriptService.getScriptVersions(scriptId);
          (script as any).versions = versions;
        }
      }
    }

    if (scripts.length === 0) {
      throw new Error('没有找到要导出的脚本');
    }

    if (options.format === 'zip') {
      return await this.createScriptZip(scripts, options);
    } else {
      return await this.createScriptJSON(scripts, options);
    }
  }

  private async createScriptZip(
    scripts: any[],
    options: ExportScriptOptions
  ): Promise<{ buffer: Buffer; filename: string; contentType: string }> {
    const zip = new JSZip();

    for (const script of scripts) {
      const filename = `${script.name}.sql`;
      zip.file(filename, script.content || '');

      // 添加元数据文件
      if (options.include_metadata) {
        const metadata = {
          id: script.id,
          name: script.name,
          description: script.description,
          tags: script.tags,
          created_at: script.created_at,
          updated_at: script.updated_at,
          created_by: script.created_by
        };
        zip.file(`${script.name}.metadata.json`, JSON.stringify(metadata, null, 2));
      }

      // 添加版本历史
      if (options.include_versions && script.versions) {
        const versionsFolder = zip.folder(`${script.n